Tracking Residential Mortgage Lending across UK Banking Sector¶


This is an interactive Jupyter notebook that uses API socket to extract latest & historic lending series from Bank of England's Interactive Database (IADB). Holdings of Mortgage securities and Lending data are aggregated across all UK Resident Banks (Monetary Financial Institutions);
Open In Colab


Notebook covers:

  • Gross Bank lending to individuals (secured on dwelling), NSA (Series Code: LPQB3VB)
  • Amounts outstanding of structured mortgage securities (assets) held on Banks' balance sheets, NSA (Series Code: LPMB7GT)
  • Value of total mortgage approvals issued to households, SA (Series Code: LPQVTVQ)
  • Write-offs of Bank mortgage lending (i.e. technical defaults & bad credit provisions), NSA (Series Code: RPQTFHD)
  • Average Quarterly BOE Bank Rate, NSA (Series Code: IUQABEDR)

All values are displayed in £ millions


How BOE bank rate impacts density of lending and holdings of securities?¶

Section 3 of notebook plots mortgage series scatter plot and distribution curve from all historic data points in a selected target range of BOE Bank Rate.


Developed by Victor Zommers | Check out other dashboards

In [6]:
%%HTML
<script src="require.js"></script>

Section 1: Data Preparation¶

Import required packages:¶


In [2]:
#!pip install requests #and other packages needed
import requests
import io
import scipy
import datetime
from datetime import timedelta
import urllib3
import warnings
warnings.filterwarnings('ignore')
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

import numpy as np
import pandas as pd
import plotly.graph_objs as go
from plotly.subplots import make_subplots
from scipy.stats import gaussian_kde
from ipywidgets import interact, FloatRangeSlider, Dropdown

Extact & Transform Mortgage data from API request¶


We will also create 2 custom series:

  • Sec_Pct - gross lending as % of all securtised mortgages held by banks (new lending as % of total mortgage assets)
  • Write_Pct - Bad loans as a % of all securtised mortgages held by banks (defaults as % of total mortgage assets)
In [3]:
boe_series = {'Datefrom':'01/Apr/1993','SeriesCodes':'IUQABEDR,LPQB3VB,LPQVTVQ,LPMB7GT,RPQTFHD,LPMB8GO','CSVF':'TN','UsingCodes':'Y','VPD':'N','VFD':'N'}
url2 = 'http://www.bankofengland.co.uk/boeapps/iadb/fromshowcolumns.asp?csv.x=yes'
headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) ' 'AppleWebKit/537.36 (KHTML, like Gecko) ' 'Chrome/54.0.2840.90 ' 'Safari/537.36'}
response = requests.get(url2, params=boe_series, headers=headers,verify = False)
df2 = pd.read_csv(io.BytesIO(response.content))
df2['DATE'] = pd.to_datetime(df2['DATE'],format='%d %b %Y')
def format_quarter_year(date):
    return f"{date.year} Q{date.quarter}"
df2['Quarter'] = df2['DATE'].apply(format_quarter_year)
sums = df2.groupby('Quarter')['LPMB7GT'].transform('sum')
df2['LPQB7GT'] = np.where(sums == 0, np.nan, sums)
sums = df2.groupby('Quarter')['LPMB8GO'].transform('sum')
df2['LPQB8GO'] = np.where(sums == 0, np.nan, sums)
df2 = df2.drop_duplicates(subset='Quarter', keep='last')
df2['Sec_Pct'] = df2['LPQB3VB']/df2['LPQB7GT']*100
df2['Write_Pct'] = df2['RPQTFHD']/df2['LPQB7GT']*100
df2.tail()
Out[3]:
DATE IUQABEDR LPQB3VB LPQVTVQ LPMB7GT RPQTFHD LPMB8GO Quarter LPQB7GT LPQB8GO Sec_Pct Write_Pct
219 2022-09-30 1.6172 76942.0 82420.0 42432.0 16.0 -940.0 2022 Q3 128892.0 -1727.0 59.694938 0.012413
222 2022-12-31 2.8056 74230.0 56510.0 40698.0 -5.0 -616.0 2022 Q4 123453.0 -1722.0 60.128146 -0.004050
225 2023-03-31 3.8555 54359.0 47424.0 39859.0 13.0 -573.0 2023 Q1 120687.0 -838.0 45.041305 0.010772
228 2023-06-30 4.4583 47843.0 57158.0 42836.0 13.0 -141.0 2023 Q2 126092.0 2977.0 37.942931 0.010310
231 2023-09-30 5.1602 57146.0 49134.0 41143.0 NaN 930.0 2023 Q3 124338.0 -1693.0 45.960205 NaN

Section 2: Data Exploration¶

Execute the cell below to plot all residential mortgage series side-by-side. Time range can be controlled by a range slider at the bottom of the plot. Use home button in top-right corner to reset the view.

More Insights:¶

Note the persistent trend of decrease of securitised mortgages held by aggregate banking sector post-GFC. This was largely replaced by holdings of financial derivatives across the balance sheets (derivatives are reported as net liability, therefore negative value of net liabilities = increase in assets).

Meanwhile, new gross lending peaked at ~60% of all securitised mortgages held in Dec 2022. This indicates cleansing of balance sheet and overall displacement of old legacy debt with new originations in the total stock of securitised mortgages.

In [4]:
fig = make_subplots(rows=7, cols=1, shared_xaxes=True)

fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['IUQABEDR'], mode='lines', name='Average BOE rate', fill='tozeroy'), row=1, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['LPQB3VB'], mode='lines', name='Gross mortgage lending by Banks', fill='tozeroy'), row=2, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['LPQVTVQ'], mode='lines', name='Value of new mortgage approvals (lead)', fill='tozeroy'), row=3, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['LPMB7GT'], mode='lines', name='Securitised mortgages held by Banks',fill='tozeroy'), row=4, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['Sec_Pct'], mode='lines', name='Gross lending as % of securitised mortgages held', fill='tozeroy'), row=5, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=df2['RPQTFHD'], mode='lines', name='Write-offs of Mortgages', fill='tozeroy'), row=6, col=1)
fig.add_trace(go.Scatter(x=df2['DATE'], y=[0 for _ in df2['DATE']], mode='lines', showlegend=False), row=7, col=1)

fig.update_yaxes(visible=False, showticklabels=False,showgrid=False, zeroline=False, row=7)
fig.update_xaxes(showgrid=False, zeroline=False,rangeslider_visible=True, row=7, col=1)
fig.update_layout(height=750, width=1000, template="simple_white")  # Adjust height and width as needed
fig.show() # render in google collab, use just fig.show() if local machine

Section 3: BOE Bank Rate range & density of mortgage lending and holdings of structured securities¶

Explore how historic density distribution of gross lending and securitisation activity changes across different monetary policy regimes.

How to use:¶

  1. Execute the code cell below after importing packages and reading the API data.
  2. Adjust the range slider and select series to explore. Wait for plotly display device to update with widget input.
In [8]:
default_column = 'LPQB3VB'
data = df2[default_column]

# Handle NaNs for Histogram and KDE
clean_data = data.replace([np.inf, -np.inf], np.nan).dropna()
kde = gaussian_kde(clean_data)
y_kde = np.linspace(clean_data.min(), clean_data.max(), 50)
x_kde = kde(y_kde)

# Create plots
scatter = go.Scatter(x=df2['DATE'], y=data, mode='markers', name='Scatter')
histogram = go.Histogram(y=clean_data, orientation='h', nbinsy=10, name='Histogram', histnorm='probability density')
kde_trace = go.Scatter(y=y_kde, x=x_kde, mode='lines', name='KDE',line=dict(color='black'))

# Combine into a single interactive figure
fig_combined = go.FigureWidget(make_subplots(rows=1, cols=2, subplot_titles=('Scatter', 'Histogram & KDE')))
fig_combined.update_layout(height=500, width=1000,template="simple_white")
fig_combined.add_trace(scatter, row=1, col=1)
fig_combined.add_trace(histogram, row=1, col=2)
fig_combined.add_trace(kde_trace, row=1, col=2)

# Update scatter plot and histogram in fig_combined based on slider and dropdown values
def update_plot(column, range_vals):
    mask = (df2['IUQABEDR'] >= range_vals[0]) & (df2['IUQABEDR'] <= range_vals[1])
    filtered_data = df2[mask]
    selected_data = filtered_data[column]
    clean_selected_data = selected_data.replace([np.inf, -np.inf], np.nan).dropna()
    # Update plots
    fig_combined.data[0].x = filtered_data['DATE'].tolist()
    fig_combined.data[0].y = selected_data.tolist()
    fig_combined.data[1].y = clean_selected_data.tolist()
    # Update KDE
    kde = gaussian_kde(clean_selected_data)
    y_kde = np.linspace(clean_selected_data.min(), clean_selected_data.max(), 50)
    x_kde = kde(y_kde)
    fig_combined.data[2].y = y_kde
    fig_combined.data[2].x = x_kde
    # Update plot render in google collab \\ remove completely if running on local machine
# Dropdown for column selection
dropdown = Dropdown(
    options=df2.columns[(df2.dtypes != 'datetime64[ns]') & (df2.dtypes != 'object')].tolist(),
    value=default_column,
    description='Series:')

# Float range slider for bank rate
min_val2, max_val2 = df2['IUQABEDR'].min(), df2['IUQABEDR'].max()
slider = FloatRangeSlider(
    value=[min_val2, max_val2],
    min=min_val2,
    max=max_val2,
    step=0.01,
    description='BOE Rate:',
    continuous_update=True)

interact(update_plot, column=dropdown, range_vals=slider)
fig_combined